/**
 * JAVACC DEMO 1.0
 */
package com.apache.database.datasource;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.apache.tools.DataMap;
import org.apache.log4j.Logger;

import com.alibaba.druid.pool.DruidDataSource;
import com.apache.cache.service.impl.LoadCacheFactory;
import com.apache.cache.util.Validator;
import com.apache.exception.BusinessException;
import com.apache.rpc.entity.RpcDatasource;
import com.apache.tools.ConfigUtil;
import com.apache.tools.StrUtil;
import com.apache.tools.ToolsMap;

/**
 * description:  数据库连接工具类
 *
 * @author dyhou 创建时间：2016-7-5
 */
public class ConnctionData {

	private Logger log = Logger.getLogger("dataSource");

	private static ConnctionData instance;

	private DruidDataSource ds;
//
//	private Map<String, DruidDataSource> dsMap = new HashMap(1);

	private ConnctionData() {
		init();
	}

	private void init() {
		String url = ConfigUtil.getInstance().getValueByKey("jdbc.properties", "jdbc.url");
		if (Validator.isNotNull(url)) {
			String name = ConfigUtil.getInstance().getValueByKey("jdbc.properties", "jdbc.driverClassName");
			String user = ConfigUtil.getInstance().getValueByKey("jdbc.properties", "jdbc.username");
			String password = ConfigUtil.getInstance().getValueByKey("jdbc.properties", "jdbc.password");
			ds = initDataSource(url, name, user, password);
		}
	}

	public DruidDataSource initDataSource(String url, String name, String user, String password) {
		DruidDataSource dsss = new DruidDataSource();
		dsss.setDriverClassName(name);
		dsss.setUrl(url);
		dsss.setUsername(user);
		dsss.setPassword(password);
		dsss.setMaxWait(60000);//最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间
		dsss.setRemoveAbandonedTimeout(180);//超过时间限制，回收没有用(废弃)的连接
		dsss.setRemoveAbandoned(true);//超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收
		/**
		 * 是否预编译SQL语句
		 * */
		dsss.setPoolPreparedStatements(true);
		return dsss;
	}

	public synchronized static ConnctionData getInstance() {
		if (null == instance) {
			instance = new ConnctionData();
		}
		return instance;
	}

	/**
	 * 执行一条简单的更新语句
	 * 执行成功则返回true
	 */

	public boolean execute(Connection conn, String sql) {
		boolean v = false;
		Statement stmt = null;//stmt用于发送sql语句到数据库并执行sql语句
		try {
			conn.setAutoCommit(true);
			stmt = conn.createStatement();
			v = stmt.execute(sql);
		} catch (SQLException e) {
			log.error("执行sql:(" + sql + ")出错；异常信息：" + e.getMessage());
			throw new BusinessException("执行sql异常:" + e.getMessage());
		} finally {
			close(conn, stmt, null);
		}
		return v;
	}

	public boolean execute(Connection conn, List<String> sqls){
		boolean v = true;
		Statement stmt = null;//stmt用于发送sql语句到数据库并执行sql语句
		try {
			stmt = conn.createStatement();
			for(String sql : sqls){
				stmt.addBatch(sql);
			}
			stmt.executeBatch();
		} catch (SQLException e) {
			throw new BusinessException("执行sql异常:" + e.getMessage());
		} finally {
			close(conn, stmt, null);
		}
		return v;
	}

	public Connection getConnection(String datasource) throws SQLException {
		DruidDataSource dbDao = null;
		datasource = StrUtil.doNull(datasource, DynamicDataSource.getInstance().getDefaultDataSourceKey());
		if (!DynamicDataSource.getInstance().checkDataSource(datasource)) {
			Object obj = LoadCacheFactory.getInstance().getCacheManager("")
					.getCacheObjectByKey("rpc_datasource_" + datasource);
			if (null == obj) {
				throw new SQLException("获取数据源["+datasource+"]失败！");
			}
			RpcDatasource rds = (RpcDatasource) obj;
			if (!DynamicDataSource.getInstance().addDataSource(rds.getSysEname(), rds.getDriverClassName(),
					rds.getJdbcUrl(), rds.getJdbcUsername(), rds.getJdbcPassword())) {
				throw new SQLException("连接数据源["+datasource+"]失败！");
			}
		}
		dbDao = DynamicDataSource.getInstance().getDataSource(datasource);
		try {
			return dbDao.getConnection();
		}catch (SQLException e){
			DynamicDataSource.getInstance().removeDataSource(datasource);
			throw new SQLException("连接数据源["+datasource+"]失败！");
		}
	}

	public void close(Connection con, Statement stmt, ResultSet rs) {
		try {
			if (null != rs)
				rs.close();
			if (null != stmt)
				stmt.close();
		} catch (SQLException e) {
			log.error("关闭连接出错；异常信息：" + e.getMessage());
		} finally {
			try {
				if (null != con)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public void close(Connection con, PreparedStatement stmt, ResultSet rs) {
		try {
			if (null != rs)
				rs.close();
			if (null != stmt)
				stmt.close();
		} catch (SQLException e) {
			log.error("关闭连接出错；异常信息：" + e.getMessage());
		} finally {
			try {
				if (null != con)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public Map selectInfo(Connection con, String sql) {
		ResultSet rs = null;
		Statement stmt = null;//stmt用于发送sql语句到数据库并执行sql语句
		DataMap<Object> fn = new DataMap<Object>();
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();
			while (rs.next()) {
				for (int i = 1; i <= count; i++) {
					String key = rsmd.getColumnLabel(i);
					String value = rs.getString(i);
					fn.put(key, value);
				}
			}
		} catch (SQLException e) {
			log.error("执行sql:(" + sql + ")出错；异常信息：" + e.getMessage());
		} finally {
			close(con, stmt, rs);
		}
		return fn;
	}

	public int selectCount(Connection con, String sql) {
		ResultSet rs = null;
		Statement stmt = null;//stmt用于发送sql语句到数据库并执行sql语句
		int count = 0;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			log.error("执行sql:(" + sql + ")出错；异常信息：" + e.getMessage());
		} finally {
			close(con, stmt, rs);
		}
		return count;
	}

	public List<Map> selectListInfo(Connection con, String sql) {
		ResultSet rs = null;
		Statement stmt = null;//stmt用于发送sql语句到数据库并执行sql语句
		List<Map> list = new ArrayList<Map>();
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();
			while (rs.next()) {
				DataMap<Object> fn = new DataMap<Object>();
				for (int i = 1; i <= count; i++) {
					String key = rsmd.getColumnLabel(i);
					String value = rs.getString(i);
					fn.put(key, value);
				}
				list.add(fn);
			}
		} catch (SQLException e) {
			log.error("执行sql:(" + sql + ")出错；异常信息：" + e.getMessage());
		} finally {
			close(con, stmt, rs);
		}
		return list;
	}

	/**
	 * 属性值生成,符合骆驼命名
	 *
	 * @param str
	 * @return
	 */
	private String toCamelNamed(String str) {
		String names[] = str.toLowerCase().split("_");
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < names.length; i++) {
			if (i == 0) {
				sb.append(names[i].toLowerCase());
			} else {
				sb.append(names[i].substring(0, 1).toUpperCase() + names[i].substring(1).toLowerCase());
			}
		}
		return sb.toString();
	}

	public String getDataBaseType(Connection con, String driverClassName, String sysEname) {
		String driverName = null;
		if (null != con) {
			try {
				driverName = con.getMetaData().getDriverName();
			} catch (SQLException e) {
				//
			}
		} else if (Validator.isNotNull(driverClassName)) {
			driverName = driverClassName;
		} else if (Validator.isNotNull(sysEname)) {
			String key = "rpc_datasource_" + sysEname;
			Object obj = LoadCacheFactory.getInstance().getCacheManager("").getCacheObjectByKey(key);
			if (null != obj) {
				RpcDatasource rds = (RpcDatasource) obj;
				return rds.getDatabaseType();
			}
		}
		if ("oracle.jdbc.OracleDriver".equals(driverName)) {
			return "oracle";
		} else if ("com.microsoft.sqlserver.jdbc.SQLServerDriver".equals(driverName)) {
			return "sqlserver";
		} else {
			return "mysql";
		}
	}

}
